iT邦幫忙

第 11 屆 iThome 鐵人賽

DAY 22
0

學習來源: codedata - MySQL 超新手入門(20)效率

  1. 索引
    1. 索引的種類
    2. 建立需要的索引
    3. 建立部份內容的索引
  2. 判斷條件的設定
  3. EXPLAIN與查詢敘述
  4. 資料維護
  5. LIMIT子句
  6. 使用暫時表格
  7. 儲存引擎

索引

_索引的種類

  1. 主索引: 值不可以重複,而且不可以儲存「NULL」值
  2. 唯一索引: 「不可重複索引」,值不可以重複,允許儲存「NULL」值
  3. 非唯一索引: 增加查詢與維護資料效率的索引,值可以重複,可以儲存「NULL」值
  4. 「FULLTEXT」索引: 「全文檢索」,提高搜尋大量文字的效率,只能用在「CHAR」、「VARCHAR」與「TEXT」型態的欄位,儲存引擎必須是「MyISAM」
  5. 「SPATIAL」索引: 「SPATIAL」型態欄位專用,儲存引擎必須是「MyISAM」

_建立需要的索引

  1. 如果很常使用這個欄位,執行條件的判斷(WHERE, ORDER BY, GROUP BY, ...),就應該為它建立索引
  2. 為了增加效率而建立索引的話,考慮下列幾點
    1. 最重要的是,不要建立沒有必要的索引,如: 資料量不大 的情況
    2. 索引的欄位儘量不要有「NULL」值
    3. 雖然某個欄位很常使用在「WHERE」、「ORDER BY」或「GROUP BY」子句中,也不一定要建立索引。例如性別欄位的值只有兩種(使用ENUM(‘M’, ‘F’)型態)
    4. 主索引鍵與 唯一索引的效率會比 非唯一索引好

_建立部份內容的索引

  1. 情況,一個 test的表格,有個 address欄位,設計255的長度,需要為地址欄位建立非唯一欄位
    CREATE TABLE test (
      ...,
      address VARCHAR(255)
    )
    CREATE INDEX addr_index ON test (address)
    
  2. 地址欄位的長度有255個字元,這樣的索引是比較沒有效率的
  3. 如果比較經常執行的條件判斷,是類似「某某縣某某市」的話,建立部份內容的索引就好
    CREATE INDEX addrindex ON test ( address (6) )
    
    1. 使用 address的前六個字元建立非唯一索引
  4. 建立索引的欄位值不應該有太多重複的值,先「分析」,欄位是不是 有很多重複的資料
    SELECT COUNT(*), COUNT(DISTINCT address)
    FROM   test;
    
  5. 或是「分析」,前六個字元 是否很多重複
    SELECT COUNT(*), COUNT( DISTINCT LEFT(address, 6) )
    FROM   test
    
  6. 確認地址欄位的前六個字元,如果有很多重複的資料,可以增加字元的數量後再查詢,直到可接受的重複數量後,再使用這個數量,建立部份內容的索引

判斷條件的設定

  1. 使用「WHERE」子句設定查詢條件,儘量使用索引來增加查詢的效率
  2. 在索引欄位使用函式或運算式,因為他訪在函式中處理,所以還是沒有使用索引
  3. 在算數運算中使用字串值,MySQL會自動幫你轉換為數字,不過處理每一筆資料,都要執行一次轉換,很沒有效率,請避免
  4. 結合查詢是一種很沒有效率的查詢,通常會幫結合條件中的欄位建立索引
  5. 使用字串樣式執行條件判斷的話,不一定會使用索引
    SELECT * FROM country WHERE name LIKE 'ta%'
    SELECT * FROM country WHERE name LIKE '%ta'
    SELECT * FROM country WHERE name LIKE '_ta%'
    
    1. 字串樣式前面是明確的值,會使用索引,如 第一行
    2. 第二跟 第三行不會使用索引
  6. 一些索引可能會包含多個欄位,依照索引欄位的順序來決定是否使用索引
    CREATE TABLE countrylanguage (
      CountryCode char(3) NOT NULL DEFAULT '',
      Language char(30) NOT NULL DEFAULT '',
      ..., 
      PRIMARY KEY (CountryCode, Language)
    )
    
    1. Language欄位在後面,如果查詢條件只有使用Language欄位的話,這個索引就不會生效

EXPLAIN 與查詢敘述

  1. MySQL 提供「EXMPLIN」: 分析一個查詢敘述,不可以使用在「SELECT」以外的敘述
  2. 「type」:
    1. ALL: 表示這個查詢發生「full table scan」,資料庫在這個表格從第一筆讀到最後一筆,才可以判斷要傳回哪些資料
    2. const: 表示只讀取一筆資料,在條件中使用 主牽引或 唯一牽引
    3. index:
  3. 「possible_keys」: MySQL用來找到資料所使用的牽引
    1. NULL: 沒有使用牽引
    2. key名稱: 使用牽引的名稱
  4. 「select_type」: 主查詢 與 子查詢

資料維護

  1. 在「UPDATE」和「DELETE」敘述中使用「WHERE」子句設定條件時,跟查詢時候該注意的地方都一樣,除了儘量使用索引來增加執行的效率,也要避免不必要的資料轉換
  2. 想要執行一個 UPDATE/DELETE 的敘述,把他轉換為查詢數續數後再使用「EXMPLIN」分析
    DELETE FROM ocuntry WHERE Code = 'YES'
    
    1. 轉換
      EXPLAIN
      SELECT * FROM country WHERE Code = 'YES'
      
    2. 一次要新增多筆資料,使用這樣的方式新增資料會是比較有效率的
      INSERT INTO cmdev.emp VALUES
      (8001, 'SIMON', 'MANAGER', 7369, '2001-02-03', 3300, NULL, 50), 
      (8002, 'Kelly', 'RNGINEER', 7370, '2003-02-03', 2300, NULL, 50),  
      (8003, 'JACK', 'PROGRAMMER', 7381, '2002-02-03', 2000, NULL, 50)   
      

LIMIT子句

  1. 這個查詢敘述只有傳回五筆資料,可是資料庫總共讀取了105筆資料,這樣的查詢會是比較沒有效率的
    SELECT * FROM country LIMIT 100, 5
    
    1. 可以使用索引與「ORDER BY」子句來增加效率
      SELECT * FROM country ORDER BY Code LIMIT 5
      

使用暫時表格

  1. 改善查詢的複雜度與效率
  2. 在查詢工作中,很常使用第一個查詢的結果,再加上不同的條件或結合,你就可以考慮使用下列的敘述:
    1. 先建立好一個暫時的表格
      CREATE TABLE countrycapital
      SELECT   Continent, co.Code, co.Name COuntryName,
               ci.Name CaptitalName, ci.Population CaptitalPop
      FROM     co.Captial = ci.ID
      WHERE    co.Captial = ci.ID
      ORDER BY Continent, CountryName
      
    2. 加入其它的條件就變得簡單多
      SELECT *
      FROM  countrycapital
      WHERE CapitalPop < 5000
      
    3. 再結合另外一個表格的話,也會比較容易
      SELECT cc.*, cola.Language OfficalLanguage
      FROM   countrycapital cc, countrylanguage cola
      WHERE  cc.Code = cola.CountryCode AND
             CapitalPop < 5000 AND IsOfficial = 'T'
      
  3. 把一個查詢放在「FROM」子句中,每次執行不同條件的查詢,資料庫都要重新執行子查詢敘述;先建立暫時的表格,再使用暫時表格執行查詢的作法會是比較有效率的

儲存引擎

  1. 允許多個用戶端同時使用的資料庫管理系統,在多用戶端的的運作環境下,資料庫就使用「鎖定、Locking」來避免資料的混亂
  2. 儲存引擎「MyISAM」
    1. 「table-level」的鎖定方式
    2. 適合使用在查詢工作非常多資料維護比較少的資料庫,這樣的資料庫運作起來的效率會比較好
  3. 儲存引擎「InnoDB」
    1. 「row-level」的鎖定方式
    2. 適合使用在查詢與資料維護工作都差不多的資料庫,這樣的資料庫運作起來的效率會比較好

上一篇
Day21. MySQL: 資料庫資訊
下一篇
Day23. MySQL: 資料庫正規化 Database normalization
系列文
網頁服務開發之路30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言